package com.dongway.core.utils;

import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.WeakHashMap;

import javax.sql.DataSource;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.util.CharArrayBuffer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.DatabaseMetaDataCallback;
import org.springframework.jdbc.support.MetaDataAccessException;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.dongway.core.annotation.AutoGeneratedKey;
import com.dongway.core.annotation.PrimaryKey;
import com.dongway.core.annotation.Table;
import com.dongway.core.common.CustomPropertyDescriptor;

public class JdbcUtils {

    private static final String GET_PREFIX = "get";
    private static final Logger logger = LoggerFactory.getLogger(JdbcUtils.class);
    private static Map<String, Set<String>> colsCache;
    private static Map<Class<?>, String> tableCache = new HashMap<Class<?>, String>();
    private static Map<String, DruidDataSource> dbCache = new HashMap<String, DruidDataSource>(1);
    private static WeakHashMap<Class<?>, List<CustomPropertyDescriptor>> cachedBeaInfo =
            new WeakHashMap<Class<?>, List<CustomPropertyDescriptor>>();

    static {
        init(JdbcUtils.getDataSource("database"));
    }


    public static DataSource getDataSource(String type) {
        DruidDataSource ds = dbCache.get(type);
        if (null == ds) {
            try {
                InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
                Properties prop = new Properties();
                prop.load(is);
                IOUtils.closeQuietly(is);
                ds = new DruidDataSource();
                Map<String, String> map = new HashMap<String, String>();
                map.put(DruidDataSourceFactory.PROP_DRIVERCLASSNAME, prop.getProperty(type + ".driver"));
                map.put(DruidDataSourceFactory.PROP_URL, prop.getProperty(type + ".url"));
                map.put(DruidDataSourceFactory.PROP_USERNAME, prop.getProperty(type + ".username"));
                map.put(DruidDataSourceFactory.PROP_PASSWORD, prop.getProperty(type + ".password"));
                Set<Object> keys = prop.keySet();
                for (Object key : keys) {
                    String _key = key.toString();
                    if (_key.startsWith("pool")) {
                        String skey = _key.substring(5);
                        map.put(skey, prop.getProperty(_key));
                    }
                }
                DruidDataSourceFactory.config(ds, map);
                dbCache.put(type, ds);
            } catch (Exception e) {
                logger.warn(e.getMessage(), e);
            }
        }
        return ds;
    }


    public static Map<String, Object> beanToMap(String tableName, Object entity) {
        if (StringUtils.isBlank(tableName)) {
            tableName = getTableName(entity.getClass());
        }
        Set<String> includes = colsCache.get(tableName);
        PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entity.getClass());
        Map<String, Object> data = new HashMap<String, Object>();
        for (PropertyDescriptor pd : pds) {
            try {
                String name = pd.getName();
                String key = camelToUnderline(name).toLowerCase();
                if (!includes.contains(key)) {
                    continue;
                }
                Object val = pd.getReadMethod().invoke(entity);
                if (null != val) {
                    data.put(key, val);
                }
            } catch (Exception e) {
                logger.info("read {} failed.", pd, e);
            }
        }
        return data;
    }


    public static void init(DataSource dataSource) {
        if (null == colsCache) {
            try {
                colsCache = new HashMap<String, Set<String>>();
                DatabaseMetaDataCallback action = new DatabaseMetaDataCallback() {

                    @Override
                    public Object processMetaData(DatabaseMetaData dbmd) throws SQLException,
                            MetaDataAccessException {
                        ResultSet rs = dbmd.getTables(null, null, null, null);
                        while (rs.next()) {
                            String tbname = rs.getString("TABLE_NAME").toLowerCase();
                            ResultSet rrs = dbmd.getColumns(null, null, tbname, null);
                            Set<String> incldes = new HashSet<String>();
                            while (rrs.next()) {
                                incldes.add(rrs.getString("COLUMN_NAME").toLowerCase());
                            }
                            com.alibaba.druid.util.JdbcUtils.close(rrs);
                            colsCache.put(tbname, incldes);
                            if (logger.isDebugEnabled()) {
                                logger.debug("{} cols {} ", tbname, incldes);
                            }
                        }
                        return null;
                    }
                };
                org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(dataSource, action);
            } catch (Exception e) {
                colsCache = null;
                logger.warn(e.getMessage(), e);
            }
        }
    }


    public static String camelToUnderline(String name) {
        if (name.toLowerCase().equals(name)) {
            return name;
        }
        char[] charArray = name.toCharArray();
        CharArrayBuffer cab = new CharArrayBuffer(name.length() + 5);
        for (char ch : charArray) {
            if (Character.isUpperCase(ch)) {
                cab.append("_");
            }
            cab.append(Character.toLowerCase(ch));
        }
        return cab.toString();
    }


    public static String getTableName(Class<?> cls) {
        if (!tableCache.containsKey(cls)) {
            Table t = cls.getAnnotation(Table.class);
            String tbname = cls.getSimpleName().toLowerCase();
            if (null != t && StringUtils.isNotBlank(t.name())) {
                tbname = StringUtils.trimToEmpty(t.name());
            }
            tableCache.put(cls, tbname);
        }
        return tableCache.get(cls);
    }


    public static void appendWhereOrAnd(StringBuilder where) {
        where.append(where.indexOf("where") == -1 ? " where " : " and ");
    }


    public static SimpleJdbcInsert creatSimpleJdbcInsert(Class<?> clazz, JdbcTemplate jdbcTemplate) {
        String tableName = JdbcUtils.getTableName(clazz);
        SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate).withTableName(tableName);
        List<CustomPropertyDescriptor> pds = cachedBeaInfo.get(clazz);
        try {
            if (pds == null) {
                pds = JdbcUtils.cachedBeanInfo(clazz);
            }
            List<String> list = new ArrayList<String>();
            for (CustomPropertyDescriptor pd : pds) {
                Field field = pd.getField();
                if (field.isAnnotationPresent(AutoGeneratedKey.class)) {
                    list.add(JdbcUtils.camelToUnderline(field.getName()));
                }
            }
            if (!list.isEmpty()) {
                insert.usingGeneratedKeyColumns(list.toArray(new String[list.size() - 1]));
            }
        } catch (Exception e) {
            logger.error("build update sql failed");
            e.printStackTrace();
        }
        return insert;
    }


    public static String buildUpdateSql(String tableName, Object entity, boolean ignoreNull) {
        StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET ");
        Class<? extends Object> clazz = entity.getClass();
        List<CustomPropertyDescriptor> pds = cachedBeaInfo.get(clazz);
        try {
            if (pds == null) {
                pds = cachedBeanInfo(clazz);
            }
            List<String> list = new ArrayList<String>();
            for (CustomPropertyDescriptor pd : pds) {
                Method method = pd.getMethod();
                Object val = method.invoke(entity);
                if (val != null || !ignoreNull) {
                    Field field = pd.getField();
                    String fieldName = field.getName();
                    if (field.isAnnotationPresent(PrimaryKey.class)) {
                        list.add(JdbcUtils.camelToUnderline(fieldName));
                    } else {
                        sql.append(JdbcUtils.camelToUnderline(fieldName) + "=:" + fieldName + ",");
                    }
                }
            }
            sql.deleteCharAt(sql.length() - 1);
            if (!list.isEmpty()) {
                sql.append(" WHERE ");
                for (String s : list) {
                    sql.append(s + "=:" + s + " and ");
                }
                sql.delete(sql.length() - 5, sql.length());
            }
        } catch (Exception e) {
            logger.error("build update sql failed");
            e.printStackTrace();
        }
        if (logger.isDebugEnabled()) {
            logger.info(sql.toString());
        }
        return sql.toString();
    }


    public static List<CustomPropertyDescriptor> cachedBeanInfo(Class<? extends Object> clazz)
            throws NoSuchMethodException {
        List<CustomPropertyDescriptor> pds;
        pds = new ArrayList<CustomPropertyDescriptor>();
        for (Field field : clazz.getDeclaredFields()) {
            CustomPropertyDescriptor pd = new CustomPropertyDescriptor();
            pd.setField(field);
            String upperCaseFirst = field.getName();
            upperCaseFirst = upperCaseFirst.substring(0, 1).toUpperCase() + upperCaseFirst.substring(1);
            pd.setMethod(clazz.getDeclaredMethod(GET_PREFIX + upperCaseFirst));
            pds.add(pd);
        }
        cachedBeaInfo.put(clazz, pds);
        return pds;
    }
}
